package com.hnkywl.sts.dao.kcgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.organ.GsjgDao;
import com.hnkywl.sts.dao.sys.YgDao;
import com.hnkywl.sts.entity.organ.Ck;
import com.hnkywl.sts.entity.organ.Gsjg;
import com.hnkywl.sts.entity.organ.Nbgs;
import com.hnkywl.sts.entity.sys.Gg;
import com.hnkywl.sts.entity.sys.Yg;
import com.hnkywl.sts.entity.xsgl.Kh;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: lenovo
 * Date: 13-4-30
 * Time: 上午11:31
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class SskcDao extends SimpleHibernateTemplate<Kh, Long> {
    private YgDao ygDao;

    private GsjgDao gsjgDao;


    /**
     * 创建实时库存中关于销售单采购单数量的子SQL语句
     *
     * @param currentDateStr 当前时间
     * @param endDateStr     结束时间
     * @return
     */
    private String createSubXsdCgdSql(String currentDateStr, String endDateStr) {
        StringBuilder sb = new StringBuilder();
        sb.append("+NVL((select sum(xs.ck_sbsl) from T_BIZ_XSMX xs,T_BIZ_XSD xsd where xs.xsd_id=xsd.id and xs.kh_id=this_.id and xsd.deleted!=1 and xsd.yck=1");
        sb.append(" and xsd.cksj between to_date('").append(endDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
        sb.append(" and to_date('").append(currentDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)");
        sb.append("-NVL((select sum(cg.rksl) from T_BIZ_CGDMX cg where cg.cgd_id=cgd4_.id and cg.kh_id=this_.id and cgd4_.deleted!=1 and cgd4_.yrk=1");
        sb.append(" and cgd4_.rksj between to_date('").append(endDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
        sb.append(" and to_date('").append(currentDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)");
        return sb.toString();
    }

    /**
     * 创建实时库存中关于转库单数量的子SQL语句
     *
     * @param currentDateStr 当前时间
     * @param endDateStr     结束时间
     * @return
     */
    private String createSubZkdSql(String currentDateStr, String endDateStr) {
        StringBuilder sb = new StringBuilder();
        sb.append("+NVL((select sum(zkdmx.zcsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.kh_id=this_.id and zkd.deleted!=1");
        sb.append(" and  zkd.zkrq between to_date('").append(endDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
        sb.append(" and to_date('").append(currentDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)");
        sb.append("-NVL((select sum(zkdmx.zrsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.new_kh_id =this_.id and zkd.deleted!=1");
        sb.append(" and  zkd.zkrq between to_date('").append(endDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
        sb.append(" and to_date('").append(currentDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)");
        return sb.toString();
    }

    /**
     * 创建实时库存中关于领料单单数量的子SQL语句
     *
     * @param currentDateStr 当前时间
     * @param endDateStr     结束时间
     * @return
     */
    private String createSubLldSql(String currentDateStr, String endDateStr) {
        StringBuilder sb = new StringBuilder();
        sb.append("+NVL((select sum(llddmx.flsl) from T_BIZ_LLDMX llddmx,T_BIZ_LLD lld where llddmx.lld_id=lld.id and llddmx.kh_id=this_.id and lld.deleted!=1 and this_.ck_id=lld.ck_id");
        sb.append(" and lld.flrq between to_date('").append(endDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
        sb.append(" and to_date('").append(currentDateStr).append(" 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)");
        return sb.toString();
    }

    /**
     * 创建库存数量的子SQL语句
     *
     * @param currentDateStr 当前时间
     * @param endDateStr     结束时间
     * @return
     */
    private String createKhSlSubSql(String currentDateStr, String endDateStr) {
        StringBuffer sf = new StringBuffer();
        sf.append("this_.sl").append(createSubXsdCgdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr));
        return sf.toString();
    }

    private String createKhRkslSubSql(String currentDateStr, String endDateStr) {
        StringBuffer sf = new StringBuffer();
        sf.append("this_.rksl").append(createSubXsdCgdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr));
        return sf.toString();
    }

    /**
     * 创建库存金额的子SQL语句
     *
     * @param currentDateStr 当前时间
     * @param endDateStr     结束时间
     * @return
     */
    private String createKhJeSubSql(String currentDateStr, String endDateStr) {
        StringBuffer sf = new StringBuffer();
        sf.append("this_.wsdj * (").append(createSubXsdCgdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr)).append(")  ");
        return sf.toString();
    }

    /**
     * 创建库存金额的子SQL语句
     *
     * @param currentDateStr 当前时间
     * @param endDateStr     结束时间
     * @return
     */
    private String createKhKkdslSubSql(String currentDateStr, String endDateStr) {
        StringBuffer sf = new StringBuffer();
        sf.append("this_.kkdsl").append(createSubXsdCgdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr))
                .append(createSubZkdSql(currentDateStr, endDateStr));
        return sf.toString();
    }

    /**
     * 查询实时库存
     *
     * @param chtbm
     * @param xhtbm
     * @param qdrqstart
     * @param qdrqend
     * @param xsxt_xsdh
     * @param carno
     * @param bindno
     * @param fhrq
     * @param ckrqstart
     * @param ckrqend
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findSskcList(String chtbm, String xhtbm, String qdrqstart, String qdrqend, String xsxt_xsdh, String carno, String bindno, String fhrq, String ckrqstart, String ckrqend, String sskczt, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = "select t.chtbm as \"chtbm\",t.xhtbm  as \"xhtbm\", t.xsxt_xsdh as \"xsdh\",t.carno as \"carno\"," +
                " t.fhrq as \"fhrq\",t.bindno as \"bindno\",t.name as \"name\", t.cz as \"cz\",t.ggxh as \"ggxh\" ,t.cd as \"cd\"," +
                " t.dw as \"dw\", t.rksl as \"rksl\", t.js as \"js\",t.kkdsl as \"kkdsl\", t.wsdj as \"wsdj\"," +
                " t.xsxt_price as \"sjdj\",  t.ck as \"ck\",t.rkrq as \"rkrq\" ,t.rkts as \"rkts\", t.ckrq as \"ckrq\"," +
                " t.wsje as \"wsje\", t.qdrq as \"qdrq\",t.rkjs as \"rkjs\",id as \"id\" ";
        String sqlBase = " (select cght.htbm as chtbm, xsht.htbm as xhtbm, kh.xsxt_xsdh,kh.carno,to_char(kh.xsxt_fhrq,'yyyy-MM-dd') as fhrq," +
                "  to_char(cght.qdrq,'yyyy-MM-dd') as qdrq,kh.bindno, gg.name,gg.cz,gg.ggxh,kh.rkjs as rkjs,kh.id as id," +
                " (select cd.z from T_SYS_SJZDZ cd where  kh.cd_sjzdz_id = cd.id) as cd,gg.dw,kh.rksl,kh.js,kh.kkdsl,kh.wsdj,kh.xsxt_price," +
                "  (kh.xsxt_price*kh.sl) as wsje,(select ck.mc  from t_sys_ck ck where kh.ck_id = ck.id) as ck,to_char(kh.rkrq,'yyyy-MM-dd') as rkrq" +
                " ,to_char(kh.ckrq,'yyyy-MM-dd') as ckrq," +
                " (select round(to_number(sysdate - cast(skh.rkrq as date))) day from t_biz_kh skh where skh.id = kh.id) as rkts " +
                " from t_biz_kh kh left join t_biz_ht cght on kh.cght_id = cght.id left join t_biz_ht xsht on cght.id = xsht.zddhcght_id" +
                " left join t_sys_gg gg on kh.jtwz_id = gg.id where kh.deleted = 0 ) t where 1=1 ";

        String sqlCountBase = " (select cght.htbm as chtbm, xsht.htbm as xhtbm, kh.xsxt_xsdh,kh.carno,to_char(kh.xsxt_fhrq,'yyyy-MM-dd') as fhrq," +
                "  kh.bindno, gg.name,gg.cz,gg.ggxh,to_char(kh.ckrq,'yyyy-MM-dd') as ckrq," +
                " (select cd.z from T_SYS_SJZDZ cd where  kh.cd_sjzdz_id = cd.id) as cd,gg.dw,kh.rksl,kh.js,kh.kkdsl,kh.wsdj,kh.xsxt_price," +
                "  kh.wsje,(select ck.mc  from t_sys_ck ck where kh.ck_id = ck.id) as ck,kh.rkrq,to_char(cght.qdrq,'yyyy-MM-dd') as qdrq," +
                " (select round(to_number(sysdate - cast(skh.rkrq as date))) day from t_biz_kh skh where skh.id = kh.id) as rkts" +
                " from t_biz_kh kh left join t_biz_ht cght on kh.cght_id = cght.id left join t_biz_ht xsht on cght.id = xsht.zddhcght_id" +
                " left join t_sys_gg gg on kh.jtwz_id = gg.id where kh.deleted = 0 ) t where 1=1 ";

        if (StringUtils.isNotEmpty(chtbm)) {
            condition.add(" t.chtbm like :chtbm ");
            param.put("chtbm", "%" + chtbm + "%");
        }

        if (StringUtils.isNotEmpty(xhtbm)) {
            condition.add(" t.xhtbm like :xhtbm ");
            param.put("xhtbm", "%" + xhtbm + "%");
        }

        if (StringUtils.isNotEmpty(qdrqstart)) {
            condition.add(" t.qdrq >= :qdrqstart");
            param.put("qdrqstart", qdrqstart);
        }

        if (StringUtils.isNotEmpty(qdrqend)) {
            condition.add(" t.qdrq <= :qdrqend");
            param.put("qdrqend", qdrqend);
        }

        if (StringUtils.isNotEmpty(xsxt_xsdh)) {
            condition.add(" t.xsxt_xsdh like :xsxt_xsdh ");
            param.put("xsxt_xsdh", "%" + xsxt_xsdh + "%");
        }

        if (StringUtils.isNotEmpty(carno)) {
            condition.add(" t.carno like :carno ");
            param.put("carno", "%" + carno + "%");
        }

        if (StringUtils.isNotEmpty(bindno)) {
            condition.add(" t.bindno like :bindno ");
            param.put("bindno", "%" + bindno + "%");
        }

        if (StringUtils.isNotEmpty(fhrq)) {
            condition.add(" t.fhrq =:fhrq ");
            param.put("fhrq", fhrq);
        }

        if (StringUtils.isNotEmpty(ckrqstart)) {
            condition.add(" t.ckrq >= :ckrqstart");
            param.put("ckrqstart", ckrqstart);
        }

        if (StringUtils.isNotEmpty(ckrqend)) {
            condition.add(" t.ckrq <= :ckrqend");
            param.put("ckrqend", ckrqend);
        }

        if (StringUtils.isNotEmpty(sskczt)) {
            if (StringUtils.equals("0", sskczt)) {
                condition.add(" t.ckrq  is null ");
            } else {
                condition.add(" t.ckrq  is not null ");
            }
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String orderSql = " order by t.chtbm desc , t.xsxt_xsdh desc , t.carno desc ";


        String countSql = "select count(1) from " + sqlCountBase + whereSql;
        String resultSql = sqlColumn + " from " + sqlBase + whereSql + orderSql;


        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * @param chtbm
     * @param xhtbm
     * @param qdrqstart
     * @param qdrqend
     * @param xsxt_xsdh
     * @param carno
     * @param bindno
     * @param fhrq
     * @param ckrqstart
     * @param ckrqend
     * @param sskczt
     * @return
     */
    public List<Map> findSskcExpList(String chtbm, String xhtbm, String qdrqstart, String qdrqend, String xsxt_xsdh, String carno, String bindno, String fhrq, String ckrqstart, String ckrqend, String sskczt) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = "select t.chtbm as \"chtbm\",t.xhtbm  as \"xhtbm\", t.xsxt_xsdh as \"xsdh\",t.carno as \"carno\"," +
                " t.fhrq as \"fhrq\",t.bindno as \"bindno\",t.name as \"name\", t.cz as \"cz\",t.ggxh as \"ggxh\" ,t.cd as \"cd\"," +
                " t.dw as \"dw\", t.rksl as \"rksl\", t.js as \"js\",t.kkdsl as \"kkdsl\", t.wsdj as \"wsdj\"," +
                " t.xsxt_price as \"sjdj\",  t.ck as \"ck\",t.rkrq as \"rkrq\" ,t.rkts as \"rkts\", t.ckrq as \"ckrq\"," +
                " t.wsje as \"wsje\", t.qdrq as \"qdrq\",t.rkjs as \"rkjs\" ";
        String sqlBase = " (select cght.htbm as chtbm, xsht.htbm as xhtbm, kh.xsxt_xsdh,kh.carno,to_char(kh.xsxt_fhrq,'yyyy-MM-dd') as fhrq," +
                "  to_char(cght.qdrq,'yyyy-MM-dd') as qdrq,kh.bindno, gg.name,gg.cz,gg.ggxh,kh.rkjs as rkjs," +
                " (select cd.z from T_SYS_SJZDZ cd where  kh.cd_sjzdz_id = cd.id) as cd,gg.dw,kh.rksl,kh.js,kh.kkdsl,kh.wsdj,kh.xsxt_price," +
                "  (kh.xsxt_price*kh.sl) as wsje,(select ck.mc  from t_sys_ck ck where kh.ck_id = ck.id) as ck,to_char(kh.rkrq,'yyyy-MM-dd') as rkrq" +
                " ,to_char(kh.ckrq,'yyyy-MM-dd') as ckrq," +
                " (select round(to_number(sysdate - cast(skh.rkrq as date))) day from t_biz_kh skh where skh.id = kh.id) as rkts " +
                " from t_biz_kh kh left join t_biz_ht cght on kh.cght_id = cght.id left join t_biz_ht xsht on cght.id = xsht.zddhcght_id" +
                " left join t_sys_gg gg on kh.jtwz_id = gg.id where kh.deleted = 0 ) t where 1=1 ";

        String sqlCountBase = " (select cght.htbm as chtbm, xsht.htbm as xhtbm, kh.xsxt_xsdh,kh.carno,to_char(kh.xsxt_fhrq,'yyyy-MM-dd') as fhrq," +
                "  kh.bindno, gg.name,gg.cz,gg.ggxh,to_char(kh.ckrq,'yyyy-MM-dd') as ckrq," +
                " (select cd.z from T_SYS_SJZDZ cd where  kh.cd_sjzdz_id = cd.id) as cd,gg.dw,kh.rksl,kh.js,kh.kkdsl,kh.wsdj,kh.xsxt_price," +
                "  kh.wsje,(select ck.mc  from t_sys_ck ck where kh.ck_id = ck.id) as ck,kh.rkrq,to_char(cght.qdrq,'yyyy-MM-dd') as qdrq," +
                " (select round(to_number(sysdate - cast(skh.rkrq as date))) day from t_biz_kh skh where skh.id = kh.id) as rkts" +
                " from t_biz_kh kh left join t_biz_ht cght on kh.cght_id = cght.id left join t_biz_ht xsht on cght.id = xsht.zddhcght_id" +
                " left join t_sys_gg gg on kh.jtwz_id = gg.id where kh.deleted = 0 ) t where 1=1 ";

        if (StringUtils.isNotEmpty(chtbm)) {
            condition.add(" t.chtbm like :chtbm ");
            param.put("chtbm", "%" + chtbm + "%");
        }

        if (StringUtils.isNotEmpty(xhtbm)) {
            condition.add(" t.xhtbm like :xhtbm ");
            param.put("xhtbm", "%" + xhtbm + "%");
        }

        if (StringUtils.isNotEmpty(qdrqstart)) {
            condition.add(" t.qdrq >= :qdrqstart");
            param.put("qdrqstart", qdrqstart);
        }

        if (StringUtils.isNotEmpty(qdrqend)) {
            condition.add(" t.qdrq <= :qdrqend");
            param.put("qdrqend", qdrqend);
        }

        if (StringUtils.isNotEmpty(xsxt_xsdh)) {
            condition.add(" t.xsxt_xsdh like :xsxt_xsdh ");
            param.put("xsxt_xsdh", "%" + xsxt_xsdh + "%");
        }

        if (StringUtils.isNotEmpty(carno)) {
            condition.add(" t.carno like :carno ");
            param.put("carno", "%" + carno + "%");
        }

        if (StringUtils.isNotEmpty(bindno)) {
            condition.add(" t.bindno like :bindno ");
            param.put("bindno", "%" + bindno + "%");
        }

        if (StringUtils.isNotEmpty(fhrq)) {
            condition.add(" t.fhrq =:fhrq ");
            param.put("fhrq", fhrq);
        }

        if (StringUtils.isNotEmpty(ckrqstart)) {
            condition.add(" t.ckrq >= :ckrqstart");
            param.put("ckrqstart", ckrqstart);
        }

        if (StringUtils.isNotEmpty(ckrqend)) {
            condition.add(" t.ckrq <= :ckrqend");
            param.put("ckrqend", ckrqend);
        }

        if (StringUtils.isNotEmpty(sskczt)) {
            if (StringUtils.equals("0", sskczt)) {
                condition.add(" t.ckrq  is null ");
            } else {
                condition.add(" t.ckrq  is not null ");
            }
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String orderSql = " order by t.chtbm desc , t.xsxt_xsdh desc , t.carno desc ";

        String resultSql = sqlColumn + " from " + sqlBase + whereSql + orderSql;

        List<Map> list = createSQLQuery(resultSql)
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    /**
     * 实时库存合计
     *
     * @param chtbm
     * @param xhtbm
     * @param qdrqstart
     * @param qdrqend
     * @param xsxt_xsdh
     * @param carno
     * @param bindno
     * @param fhrq
     * @param ckrqstart
     * @param ckrqend
     * @return
     */
    public List<Map> findSumSskc(String chtbm, String xhtbm, String qdrqstart, String qdrqend, String xsxt_xsdh, String carno, String bindno, String fhrq, String ckrqstart, String ckrqend, String sskczt) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String sql = "select nvl(sum(kh.rkjs),0) as \"rkjs\",nvl(sum(kh.rksl),0) as \"rksl\",nvl(sum(kh.js),0) as \"js\"," +
                " nvl(sum(kh.kkdsl),0) as \"kkdsl\", nvl(sum(kh.xsxt_price*kh.sl),0) as \"sjje\" from t_biz_kh kh " +
                " left join t_biz_ht cght on kh.cght_id = cght.id left join t_biz_ht xsht on cght.id = xsht.zddhcght_id" +
                "  left join t_sys_gg gg on kh.jtwz_id = gg.id where kh.deleted = 0 ";

        if (StringUtils.isNotEmpty(chtbm)) {
            condition.add(" cght.htbm like :chtbm ");
            param.put("chtbm", "%" + chtbm + "%");
        }

        if (StringUtils.isNotEmpty(xhtbm)) {
            condition.add(" xsht.htbm like :xhtbm ");
            param.put("xhtbm", "%" + xhtbm + "%");
        }

        if (StringUtils.isNotEmpty(qdrqstart)) {
            condition.add(" to_char(cght.qdrq,'yyyy-MM-dd') >= :qdrqstart");
            param.put("qdrqstart", qdrqstart);
        }

        if (StringUtils.isNotEmpty(qdrqend)) {
            condition.add(" to_char(cght.qdrq,'yyyy-MM-dd') <= :qdrqend");
            param.put("qdrqend", qdrqend);
        }

        if (StringUtils.isNotEmpty(xsxt_xsdh)) {
            condition.add(" kh.xsxt_xsdh like :xsxt_xsdh ");
            param.put("xsxt_xsdh", "%" + xsxt_xsdh + "%");
        }

        if (StringUtils.isNotEmpty(carno)) {
            condition.add(" kh.carno like :carno ");
            param.put("carno", "%" + carno + "%");
        }

        if (StringUtils.isNotEmpty(bindno)) {
            condition.add(" kh.bindno like :bindno ");
            param.put("bindno", "%" + bindno + "%");
        }

        if (StringUtils.isNotEmpty(fhrq)) {
            condition.add(" to_char(kh.xsxt_fhrq,'yyyy-MM-dd') =:fhrq ");
            param.put("fhrq", fhrq);
        }

        if (StringUtils.isNotEmpty(ckrqstart)) {
            condition.add(" to_char(kh.ckrq,'yyyy-MM-dd') >= :ckrqstart");
            param.put("ckrqstart", ckrqstart);
        }

        if (StringUtils.isNotEmpty(ckrqend)) {
            condition.add(" to_char(kh.ckrq,'yyyy-MM-dd') <= :ckrqend");
            param.put("ckrqend", ckrqend);
        }

        if (StringUtils.isNotEmpty(sskczt)) {
            if (StringUtils.equals("0", sskczt)) {
                condition.add(" kh.ckrq  is null ");
            } else {
                condition.add(" kh.ckrq  is not null ");
            }
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        sql += whereSql;

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    public Page<Map> findList(String xsxt_xsdh, String sbgsJc, String cggsJc, String code, String ckmc, Long gysId, Long ckId, String jtwzname, String cgdh, String khbm, String qk, Date endDate, String wzmc, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String endDateStr = formatDate(endDate);
        String currentDateStr = formatDate(new Date());

        String sqlColumn = "select t.id as \"id\",t.rkts  as \"rkts\", t.khgys_jc as \"khgys_jc\",t.khbm as \"khbm\",t.xsxt_htbm as \"xsxt_htbm\",t.xsxt_xsdh as \"xsxt_xsdh\",t.carNo as \"carNo\",t.bindNo as \"bindNo\", t.qk as \"qk\", t.qkr as \"qkr\",t.jtwz_id as \"jtwz_id\"," +
                " t.jtwz_code as \"jtwz_code\",t.jtwz_oldcode as \"jtwz_oldcode\", t.jtwz_name as \"jtwz_name\",t.jtwz_cz as \"jtwz_cz\" ,t.jtwz_ggxh as \"jtwz_ggxh\"," +
                " t.cgd_id as \"cgd_id\",t.cgd_cgdh as \"cgd_cgdh\", t.cd_id as \"cd_id\", t.cd_z as \"cd_z\",t.ccrq as \"ccrq\", t.sjdj as \"sjdj\", " +
                "  t.dw_z as \"dw_z\", t.ck_id as \"ck_id\", t.ck_mc as \"ck_mc\",rksl as \"rksl\" ,ckrq as \"ckrq\", " +
                "  t.sl as \"sl\",t.bz as \"bz\",t.chd as \"chd\",t.js as \"js\",t.jz as \"jz\",t.wsdj as \"wsdj\"," +
                " t.wsje as \"wsje\",t.zlqk as \"zlqk\",t.rkdh as \"rkdh\", t.kkdsl as \"kkdsl\", t.lastUpdated as \"lastUpdated\",t.rkrq as \"rkrq\" ," +
                "t.sbgs_jc as \"sbgs_jc\",t.sbgs_id as \"sbgs_id\",t.sbbm_id as \"sbbm_id\",t.sbbm_qc as \"sbbm_qc\",t.cggs_id as \"cggs_id\"," +
                "t.cggs_jc as \"cggs_jc\",t.createuser_id as \"createuser_id\"";
        String sqlBase = " (select this_.id as id,this_.xsxt_htbm as xsxt_htbm,this_.xsxt_xsdh as xsxt_xsdh,this_.carNo as carNo,this_.bindNo as bindNo,khgys.jc as khgys_jc, khbm as khbm,qk as qk," +
                "(select createuser5_.xm from T_SYS_YG createuser5_  where this_.CREATEUSER_ID =createuser5_.id) as qkr," +
                "jtwz1_.id as jtwz_id,jtwz1_.code as jtwz_code,jtwz1_.oldcode as jtwz_oldcode,jtwz1_.name as jtwz_name," +
                "jtwz1_.cz as jtwz_cz ,jtwz1_.ggxh as jtwz_ggxh,cgd4_.id as cgd_id,this_.xsxt_price as sjdj," +
                "cgd4_.cgdh as cgd_cgdh,this_.CD_SJZDZ_ID as cd_id, (select cd2_.z from T_SYS_SJZDZ cd2_ where  this_.CD_SJZDZ_ID = cd2_.id) as cd_z," +
                "jtwz1_.dw as dw_z, this_.CK_ID as ck_id,(select ck3_.mc  from T_SYS_CK ck3_ where this_.CK_ID = ck3_.id) as ck_mc," +
                "this_.bz as bz,this_.chd as chd,this_.js as js,this_.jz as jz,this_.wsdj as wsdj," +
                "this_.zlqk as zlqk,cgd4_.gys_id as gys,this_.rkdh as rkdh,this_.ckrq as ckrq,to_char(this_.xsxt_fhrq,'yyyy-MM-dd') as ccrq," +
                "this_.last_updated as lastUpdated,this_.rkrq as rkrq,(select round(to_number(sysdate - cast(skh.rkrq as date))) day from t_biz_kh skh where skh.id = this_.id) as rkts, this_.sbgs_id as sbgs_id," +
                "(select g1_.jc from t_SYS_GSJG g1_ where this_.sbgs_id = g1_.id)  as sbgs_jc, this_.sbbm_id as sbbm_id," +
                "(select g2_.qc from t_SYS_GSJG g2_ where this_.sbbm_id = g2_.id) as sbbm_qc,this_.cggs_id as cggs_id," +
                "(select g3_.jc from t_SYS_GSJG g3_ where this_.cggs_id = g3_.id)  as  cggs_jc,this_.CREATEUSER_ID as createuser_id," +
                "this_.CREATEORGAN_ID as CREATEORGAN_ID,this_.Createdepartment_Id as Createdepartment_Id," +
                createKhSlSubSql(currentDateStr, endDateStr) + "as sl," +
                createKhRkslSubSql(currentDateStr, endDateStr) + "as rksl," +
                createKhJeSubSql(currentDateStr, endDateStr) + "as wsje," +
                createKhKkdslSubSql(currentDateStr, endDateStr) + " as kkdsl" +
                " from T_BIZ_KH this_ " + /*yggl +*/ " left outer join t_sys_gsjg khgys on this_.GYS_ID = khgys.id " +
                " left outer join T_BIZ_CGD cgd4_ on this_.CGD_ID = cgd4_.id" +
                " left outer join T_SYS_GG jtwz1_ on this_.JTWZ_ID = jtwz1_.id where this_.deleted = 0 ) t";

        String sqlCountBase = " (select khbm,jtwz1_.id as jtwz_id,jtwz1_.code as jtwz_code,jtwz1_.oldcode as jtwz_oldcode,jtwz1_.name as jtwz_name," +
                "jtwz1_.cz||' '||jtwz1_.ggxh as jtwz_ggxh,this_.CK_ID as ck_id,cgd4_.gys_id as gys,qk as qk," +
                "(select g1_.jc from t_SYS_GSJG g1_ where this_.sbgs_id = g1_.id)  as sbgs_jc, " +
                "(select g3_.jc from t_SYS_GSJG g3_ where this_.cggs_id = g3_.id)  as  cggs_jc, " +
                "this_.sbgs_id as sbgs_id ,this_.sbbm_id as sbbm_id, this_.cggs_id as cggs_id,this_.xsxt_xsdh as xsxt_xsdh,this_.rkrq as rkrq, " +
                "this_.CREATEUSER_ID as createuser_id,this_.CREATEORGAN_ID as CREATEORGAN_ID,this_.Createdepartment_Id as Createdepartment_Id " +
                "from T_BIZ_KH this_ " + /*yggl +*/ " left outer join t_sys_gsjg khgys on khgys.id = this_.GYS_ID " +
                "left outer join T_BIZ_CGD cgd4_ on this_.CGD_ID = cgd4_.id " +
                "left outer join T_SYS_GG jtwz1_ on this_.JTWZ_ID = jtwz1_.id where this_.deleted = 0 ) t";
        if (StringUtils.isNotEmpty(xsxt_xsdh)) {
            condition.add(" t.xsxt_xsdh like :xsxt_xsdh ");
            param.put("xsxt_xsdh", "%" + xsxt_xsdh + "%");
        }

        if (StringUtils.isNotEmpty(khbm)) {
            condition.add(" t.khbm like :khbm ");
            param.put("khbm", "%" + khbm + "%");
        }

        if (StringUtils.isNotEmpty(jtwzname)) {
            condition.add(" t.jtwz_ggxh like:jtwz_ggxh ");
            param.put("jtwz_ggxh", "%" + jtwzname + "%");
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" (t.jtwz_code like :jtwz_code or t.jtwz_oldcode like :jtwz_oldcode) ");
            param.put("jtwz_code", code + "%");
            param.put("jtwz_oldcode", code + "%");
        }

        if (ckId != null) {
            condition.add(" t.ck_id=:ck_id ");
            param.put("ck_id", ckId);
        }

        if (StringUtils.isNotEmpty(sbgsJc)) {
            Gsjg g = this.gsjgDao.get(Long.valueOf(sbgsJc));
            if (g instanceof Nbgs) {
                condition.add(" t.sbgs_id = :sbgs_id ");
                param.put("sbgs_id", g.getId());
            } else {
                condition.add(" t.sbbm_id = :sbbm_id ");
                param.put("sbbm_id", g.getId());
            }
        }

        if (StringUtils.isNotEmpty(cggsJc)) {
            condition.add(" t.cggs_id = :cggs_id ");
            param.put("cggs_id", cggsJc);
        }

        if (StringUtils.isNotEmpty(qk)) {
            condition.add(" t.qk=:qk ");
            param.put("qk", qk.equals("true"));
        }

        if (gysId != null) {
            condition.add(" t.gys=:gysId ");
            param.put("gysId", gysId);
        }

        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" t.jtwz_name like :wzmc ");
            param.put("wzmc", wzmc);
        }

        condition.add(" t.rkrq<=to_date('" + endDateStr + " 23:59:59','yyyy-MM-dd hh24:mi:ss') ");

//        String joinTableSql = " inner join t_sys_yg yg on yg.id=t.createuser_id ";
        //String dataRegion = RegionDao.createSQLDataRegion(null, "t");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by t.id desc";


        String countSql = "select count(1) from " + sqlCountBase + whereSql + " and " + createSqlCkDataRegion(null, "t");
        String resultSql = sqlColumn + " from (" + sqlBase + ") " + whereSql + " and " + createSqlCkDataRegion(null, "t") + orderSql;


        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 查询商户物资信息
     *
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findBuyerInfoList(String code, String wzmc, String ggxh, String gysName, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String sqlColumn = "select jhdw.qc,\n" +
                "       g.name,\n" +
                "       g.ggxh,\n" +
                "       g.code,\n" +
                "       g.cz,\n" +
                "       g.dw,\n" +
                "       cd.z,\n" +
                "       nvl(jhmx.jhjs,0) as \"sbjs\",\n" +
                "       nvl(jhmx.jhsl,0) as \"sbsl\",\n" +
                "       nvl((select sum(xsmx.ck_js) from t_biz_xsmx xsmx left join t_biz_kh k on xsmx.kh_id = k.id where  k.deleted = 0 and k.id in (select id from t_biz_kh k1 where k1.bgjhmx_id = jhmx.id and k1.sbgs_id = jhdw.id and k1.deleted = 0)),0) as \"ckjs\",\n" +
                "       nvl((select sum(xsmx.ck_sbsl) from t_biz_xsmx xsmx left join t_biz_kh k on xsmx.kh_id = k.id where  k.deleted = 0 and k.id in (select id from t_biz_kh k1 where k1.bgjhmx_id = jhmx.id and k1.sbgs_id = jhdw.id and k1.deleted = 0)),0) as \"cksl\",\n" +
                "       nvl((select sum(k5.cshjs) from t_biz_kh k5 where k5.bgjhmx_id = jhmx.id and k5.sbgs_id = jhdw.id and k5.deleted = 0),0) as \"rkjs\",\n" +
                "       nvl((select sum(k5.cshjs) from t_biz_kh k5 where k5.bgjhmx_id = jhmx.id and k5.sbgs_id = jhdw.id and k5.deleted = 0),0) as \"rksl\",      \n" +
                "       nvl(jhmx.jhjs,0) - nvl((select sum(k4.cshjs) from t_biz_kh k4 where k4.bgjhmx_id = jhmx.id and k4.sbgs_id = jhdw.id and k4.deleted = 0),0) as \"syjs\",\n" +
                "       nvl(jhmx.jhsl,0) - nvl((select sum(k4.cshsl) from t_biz_kh k4 where k4.bgjhmx_id = jhmx.id and k4.sbgs_id = jhdw.id and k4.deleted = 0),0) as \"sysl\",\n" +
                "       nvl((select sum(k3.gdjshj) from t_biz_kh k3 where k3.bgjhmx_id = jhmx.id and k3.sbgs_id = jhdw.id and k3.deleted = 0),0) as \"gdjs\",\n" +
                "       nvl((select sum(k3.gdslhj) from t_biz_kh k3 where k3.bgjhmx_id = jhmx.id and k3.sbgs_id = jhdw.id and k3.deleted = 0),0) as \"gdsl\"  ";
        String sqlBase = " from t_biz_bgjhmx jhmx\n" +
                "  left join t_biz_bgjh jh\n" +
                "    on jhmx.bgjh_id = jh.id\n" +
                "  left join t_sys_gsjg jhdw\n" +
                "    on jh.jhdw_id = jhdw.id\n" +
                "  left join t_sys_gg g\n" +
                "    on jhmx.wzgg_id = g.id\n" +
                "  left join t_sys_sjzdz cd\n" +
                "    on jhmx.sjzdz_cd_id = cd.id\n" +
                " where jhmx.deleted = 0 and jh.deleted = 0 ";
        if (StringUtils.isNotEmpty(gysName)) {
            condition.add(" QC like:gysName ");
            param.put("gysName", "%" + gysName.trim() + "%");
        }
        if (StringUtils.isNotEmpty(code)) {
            condition.add(" CODE like:code ");
            param.put("code", "%" + code.trim() + "%");
        }
        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" NAME like:wzmc ");
            param.put("wzmc", "%" + wzmc.trim() + "%");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            condition.add(" GGXH like:ggxh ");
            param.put("ggxh", "%" + ggxh.trim() + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        String countSql = "select count(1) from (" + sqlColumn + sqlBase + ") where 1=1 " + whereSql;
        String resultSql = sqlColumn + sqlBase + whereSql;

        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 查询合同盈亏报表
     *
     * @param gysName
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findYkbbList(String htbm, String gysName, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = "select cght.htbm,\n" +
                "       gys.qc,\n" +
                "       NVL((select sum(nvl(kh.cshsl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0) as \"cgje\",\n" +
                "        NVL((select sum(nvl(kh.sl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0) as \"kcje\",\n" +
                "       NVL((select sum(nvl(xsmx.bhsj, 0) * nvl(xsmx.ck_sbsl, 0))\n" +
                "             from t_biz_xsd x\n" +
                "             left join t_biz_xsmx xsmx\n" +
                "               on xsmx.xsd_id = x.id\n" +
                "             left join t_biz_kh kh\n" +
                "               on xsmx.kh_id = kh.id\n" +
                "             left join t_biz_cgdmx cgdmx\n" +
                "               on kh.cgdmx_id = cgdmx.id\n" +
                "            where x.deleted = 0\n" +
                "              and xsmx.deleted = 0\n" +
                "              and cgdmx.deleted = 0\n" +
                "              and kh.deleted = 0\n" +
                "              and cgdmx.id in (select cgdmx.id\n" +
                "                                 from t_biz_cgd cgd\n" +
                "                                 left join t_biz_cgdmx cgdmx\n" +
                "                                   on cgdmx.cgd_id = cgd.id\n" +
                "                                where cgd.deleted = 0\n" +
                "                                  and cgdmx.deleted = 0\n" +
                "                                  and cgd.cght_id = cght.id)),\n" +
                "           0) as \"xsje\",\n" +
                "       (NVL((select sum(nvl(xsmx.bhsj, 0) * nvl(xsmx.ck_sbsl, 0))\n" +
                "              from t_biz_xsd x\n" +
                "              left join t_biz_xsmx xsmx\n" +
                "                on xsmx.xsd_id = x.id\n" +
                "              left join t_biz_kh kh\n" +
                "                on xsmx.kh_id = kh.id\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where x.deleted = 0\n" +
                "               and xsmx.deleted = 0\n" +
                "               and cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0) +\n" +
                "       NVL((select sum(nvl(kh.sl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0) - NVL((select sum(nvl(kh.cshsl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0)) as \"ykje\" ";
        String sqlBase = " from t_biz_ht cght\n" +
                "  left join t_sys_gsjg gys\n" +
                "    on cght.hzf_id = gys.id\n" +
                " where cght.type = 'cght'\n" +
                "   and cght.deleted = 0 ";
        if (StringUtils.isNotEmpty(gysName)) {
            condition.add(" QC like:gysName ");
            param.put("gysName", "%" + gysName.trim() + "%");
        }
        if (StringUtils.isNotEmpty(htbm)) {
            condition.add(" HTBM like:htbm ");
            param.put("htbm", "%" + htbm.trim() + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        String countSql = "select count(1) from (" + sqlColumn + sqlBase + ") where 1=1 " + whereSql;
        String resultSql = sqlColumn + sqlBase + whereSql;

        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 查询合同盈亏报表合计
     *
     * @param gysName
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findYkbbListToSum(String htbm, String gysName, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = "select SUM(NVL((select sum(nvl(kh.cshsl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0)) as \"cgje\",\n" +
                "       SUM(NVL((select sum(nvl(kh.sl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0)) as \"kcje\",\n" +
                "       SUM(NVL((select sum(nvl(xsmx.bhsj, 0) * nvl(xsmx.ck_sbsl, 0))\n" +
                "             from t_biz_xsd x\n" +
                "             left join t_biz_xsmx xsmx\n" +
                "               on xsmx.xsd_id = x.id\n" +
                "             left join t_biz_kh kh\n" +
                "               on xsmx.kh_id = kh.id\n" +
                "             left join t_biz_cgdmx cgdmx\n" +
                "               on kh.cgdmx_id = cgdmx.id\n" +
                "            where x.deleted = 0\n" +
                "              and xsmx.deleted = 0\n" +
                "              and cgdmx.deleted = 0\n" +
                "              and kh.deleted = 0\n" +
                "              and cgdmx.id in (select cgdmx.id\n" +
                "                                 from t_biz_cgd cgd\n" +
                "                                 left join t_biz_cgdmx cgdmx\n" +
                "                                   on cgdmx.cgd_id = cgd.id\n" +
                "                                where cgd.deleted = 0\n" +
                "                                  and cgdmx.deleted = 0\n" +
                "                                  and cgd.cght_id = cght.id)),\n" +
                "           0)) as \"xsje\",\n" +
                "       SUM((NVL((select sum(nvl(xsmx.bhsj, 0) * nvl(xsmx.ck_sbsl, 0))\n" +
                "              from t_biz_xsd x\n" +
                "              left join t_biz_xsmx xsmx\n" +
                "                on xsmx.xsd_id = x.id\n" +
                "              left join t_biz_kh kh\n" +
                "                on xsmx.kh_id = kh.id\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where x.deleted = 0\n" +
                "               and xsmx.deleted = 0\n" +
                "               and cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0) +\n" +
                "       NVL((select sum(nvl(kh.sl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0) - NVL((select sum(nvl(kh.cshsl, 0) * nvl(kh.wsdj, 0))\n" +
                "              from t_biz_kh kh\n" +
                "              left join t_biz_cgdmx cgdmx\n" +
                "                on kh.cgdmx_id = cgdmx.id\n" +
                "             where cgdmx.deleted = 0\n" +
                "               and kh.deleted = 0\n" +
                "               and cgdmx.id in (select cgdmx.id\n" +
                "                                  from t_biz_cgd cgd\n" +
                "                                  left join t_biz_cgdmx cgdmx\n" +
                "                                    on cgdmx.cgd_id = cgd.id\n" +
                "                                 where cgd.deleted = 0\n" +
                "                                   and cgdmx.deleted = 0\n" +
                "                                   and cgd.cght_id = cght.id)),\n" +
                "            0))) as \"ykje\" ";
        String sqlBase = "  from t_biz_ht cght\n" +
                "  left join t_sys_gsjg gys\n" +
                "    on cght.hzf_id = gys.id\n" +
                " where cght.type = 'cght'\n" +
                "   and cght.deleted = 0 ";
        if (StringUtils.isNotEmpty(gysName)) {
            condition.add(" QC like:gysName ");
            param.put("gysName", "%" + gysName.trim() + "%");
        }
        if (StringUtils.isNotEmpty(htbm)) {
            condition.add(" HTBM like:htbm ");
            param.put("htbm", "%" + htbm.trim() + "%");
        }
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        String resultSql = sqlColumn + sqlBase + whereSql;

        List<Map> list = createSQLQuery(resultSql)
                .setProperties(param)
                .setFirstResult((pageNo - 1) * pageSize)
                .setMaxResults(pageSize)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        page.setResult(list);
        return page;
    }

    /**
     * 查询商权柱状图
     *
     * @return
     */
    public Page<Map> findByifCharts(String code, String wzmc, String ggxh, String gysName, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String sqlColumn = "select jhdw.qc,\n" +
                "       jhdw.id,\n" +
                "       SUM(nvl(jhmx.jhsl,0)) as \"sbsl\",\n" +
                "       SUM(nvl((select sum(k5.cshjs) from t_biz_kh k5 where k5.bgjhmx_id = jhmx.id and k5.sbgs_id = jhdw.id and k5.deleted = 0),0)) as \"rksl\",      \n" +
                "       ABS(SUM(nvl(jhmx.jhsl,0) - nvl((select sum(k4.cshsl) from t_biz_kh k4 where k4.bgjhmx_id = jhmx.id and k4.sbgs_id = jhdw.id and k4.deleted = 0),0))) as \"wfpsl\" ";
        String sqlBase = "  from t_biz_bgjhmx jhmx\n" +
                "  left join t_biz_bgjh jh\n" +
                "    on jhmx.bgjh_id = jh.id\n" +
                "  left join t_sys_gsjg jhdw\n" +
                "    on jh.jhdw_id = jhdw.id\n" +
                "  left join t_sys_gg g\n" +
                "    on jhmx.wzgg_id = g.id\n" +
                "  left join t_sys_sjzdz cd\n" +
                "    on jhmx.sjzdz_cd_id = cd.id\n" +
                " where jhmx.deleted = 0 and jh.deleted = 0";
        if (StringUtils.isNotEmpty(gysName)) {
            condition.add(" QC like:gysName ");
            param.put("gysName", "%" + gysName.trim() + "%");
        }
        if (StringUtils.isNotEmpty(code)) {
            condition.add(" CODE like:code ");
            param.put("code", "%" + code.trim() + "%");
        }
        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" NAME like:wzmc ");
            param.put("wzmc", "%" + wzmc.trim() + "%");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            condition.add(" GGXH like:ggxh ");
            param.put("ggxh", "%" + ggxh.trim() + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String groupBySql = "group by jhdw.qc,jhdw.id";
        String resultSql = sqlColumn + sqlBase + whereSql + groupBySql;

        List<Map> list = createSQLQuery(resultSql)
                .setProperties(param)
                .setFirstResult((pageNo - 1) * pageSize)
                .setMaxResults(pageSize)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        page.setResult(list);
        return page;
    }

    /**
     * 商权信息查询数合计
     *
     * @param code
     * @param wzmc
     * @param ggxh
     * @param gysName
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findBuyerInfoListToSum(String code, String wzmc, String ggxh, String gysName, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String sqlColumn = "select SUM(nvl(jhmx.jhjs,0)) as \"sbjs\",\n" +
                "       SUM(nvl(jhmx.jhsl,0)) as \"sbsl\",\n" +
                "       SUM(nvl((select sum(xsmx.ck_js) from t_biz_xsmx xsmx left join t_biz_kh k on xsmx.kh_id = k.id where  k.deleted = 0 and k.id in (select id from t_biz_kh k1 where k1.bgjhmx_id = jhmx.id and k1.sbgs_id = jhdw.id and k1.deleted = 0)),0)) as \"ckjs\",\n" +
                "       SUM(nvl((select sum(xsmx.ck_sbsl) from t_biz_xsmx xsmx left join t_biz_kh k on xsmx.kh_id = k.id where  k.deleted = 0 and k.id in (select id from t_biz_kh k1 where k1.bgjhmx_id = jhmx.id and k1.sbgs_id = jhdw.id and k1.deleted = 0)),0)) as \"cksl\",\n" +
                "       SUM(nvl((select sum(k5.cshjs) from t_biz_kh k5 where k5.bgjhmx_id = jhmx.id and k5.sbgs_id = jhdw.id and k5.deleted = 0),0)) as \"rkjs\",\n" +
                "       SUM(nvl((select sum(k5.cshjs) from t_biz_kh k5 where k5.bgjhmx_id = jhmx.id and k5.sbgs_id = jhdw.id and k5.deleted = 0),0)) as \"rksl\",      \n" +
                "       SUM(nvl(jhmx.jhjs,0) - nvl((select sum(k4.cshjs) from t_biz_kh k4 where k4.bgjhmx_id = jhmx.id and k4.sbgs_id = jhdw.id and k4.deleted = 0),0)) as \"syjs\",\n" +
                "       SUM(nvl(jhmx.jhsl,0) - nvl((select sum(k4.cshsl) from t_biz_kh k4 where k4.bgjhmx_id = jhmx.id and k4.sbgs_id = jhdw.id and k4.deleted = 0),0)) as \"sysl\",\n" +
                "       SUM(nvl((select sum(k3.gdjshj) from t_biz_kh k3 where k3.bgjhmx_id = jhmx.id and k3.sbgs_id = jhdw.id and k3.deleted = 0),0)) as \"gdjs\",\n" +
                "       SUM(nvl((select sum(k3.gdslhj) from t_biz_kh k3 where k3.bgjhmx_id = jhmx.id and k3.sbgs_id = jhdw.id and k3.deleted = 0),0)) as \"gdsl\" ";
        String sqlBase = " from t_biz_bgjhmx jhmx\n" +
                "  left join t_biz_bgjh jh\n" +
                "    on jhmx.bgjh_id = jh.id\n" +
                "  left join t_sys_gsjg jhdw\n" +
                "    on jh.jhdw_id = jhdw.id\n" +
                "  left join t_sys_gg g\n" +
                "    on jhmx.wzgg_id = g.id\n" +
                "  left join t_sys_sjzdz cd\n" +
                "    on jhmx.sjzdz_cd_id = cd.id\n" +
                " where jhmx.deleted = 0 and jh.deleted = 0 ";
        if (StringUtils.isNotEmpty(gysName)) {
            condition.add(" QC like:gysName ");
            param.put("gysName", "%" + gysName.trim() + "%");
        }
        if (StringUtils.isNotEmpty(code)) {
            condition.add(" CODE like:code ");
            param.put("code", "%" + code.trim() + "%");
        }
        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" NAME like:wzmc ");
            param.put("wzmc", "%" + wzmc.trim() + "%");
        }
        if (StringUtils.isNotEmpty(ggxh)) {
            condition.add(" GGXH like:ggxh ");
            param.put("ggxh", "%" + ggxh.trim() + "%");
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        String resultSql = sqlColumn + sqlBase + whereSql;

        List<Map> list = createSQLQuery(resultSql)
                .setProperties(param)
                .setFirstResult((pageNo - 1) * pageSize)
                .setMaxResults(pageSize)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        page.setResult(list);
        return page;
    }

    public List<Map> findSumKh(String xsxt_xsdh, String sbgsJc, String cggsJc, String code, String ckmc, Long gysId, Long ckId, String jtwzname, String cgdh, String khbm, String qk, Date endDate, String wzmc) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String endDateStr = formatDate(endDate);
        String currentDateStr = formatDate(new Date());

//        String yggl = StringUtils.EMPTY;
//        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
//            yggl = " inner join v_wzbm_gg_yg_search bmfw  on t.JTWZ_ID = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId();
//            //where = " bmfw.yg_id = " + YgService.getCurrentYg().getId();
//        } else if (existsSQLQuery("select 1 from v_wzbm_gg_search  where gsid = ?", YgService.getCurrentYgGsId())) {
//            yggl = " inner join v_wzbm_gg_search bmfw on t.JTWZ_ID = bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId();
//        }

        String sql = "select sum(" + createKhSlSubSql(currentDateStr, endDateStr) + ") as \"sumsl\"," +
                "sum(" + createKhKkdslSubSql(currentDateStr, endDateStr) + ") as \"sumkkdsl\"," +
                "sum(" + createKhJeSubSql(currentDateStr, endDateStr) + ") as \"wsje\"," +
                "sum(" + createKhRkslSubSql(currentDateStr, endDateStr) + ") as \"sumrksl\"," +
                "sum(this_.js) as \"js\"" +
                "from t_biz_kh this_ " + //yggl +
                " left outer join T_SYS_YG createuser5_ on this_.CREATEUSER_ID =createuser5_.id" +
                " left outer join t_SYS_GSJG g1_ on this_.sbgs_id = g1_.id" +
                " left outer join t_SYS_GSJG g2_ on this_.sbbm_id = g2_.id" +
                " left outer join t_SYS_GSJG g3_ on this_.cggs_id = g3_.id" +
                " left outer join T_SYS_SJZDZ cd2_ on this_.CD_SJZDZ_ID = cd2_.id" +
                " left outer join T_BIZ_CGD cgd4_ on this_.CGD_ID = cgd4_.id" +
                " left outer join T_SYS_CK ck3_ on this_.CK_ID = ck3_.id" +
                " left outer join T_SYS_GG jtwz1_ on this_.JTWZ_ID = jtwz1_.id ";
        if (StringUtils.isNotEmpty(xsxt_xsdh)) {
            condition.add(" this_.xsxt_xsdh like :xsxt_xsdh ");
            param.put("xsxt_xsdh", "%" + xsxt_xsdh + "%");
        }

        if (StringUtils.isNotEmpty(khbm)) {
            condition.add(" this_.khbm like :khbm ");
            param.put("khbm", "%" + khbm + "%");
        }

        if (StringUtils.isNotEmpty(jtwzname)) {
            condition.add(" (jtwz1_.cz||' '||jtwz1_.ggxh like :jtwz_ggxh) ");
            param.put("jtwz_ggxh", "%" + jtwzname + "%");
            param.put("jtwz_cz", "%" + jtwzname + "%");
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" (jtwz1_.code like :jtwz_code or jtwz1_.oldcode like :jtwz_oldcode) ");
            param.put("jtwz_code", code + "%");
            param.put("jtwz_oldcode", code + "%");
        }

        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" jtwz1_.name like :wzmc ");
            param.put("wzmc", wzmc);
        }

        if (ckId != null) {
            condition.add(" ck3_.id=:ck_id ");
            param.put("ck_id", ckId);
        }
        if (StringUtils.isNotEmpty(sbgsJc)) {
            Gsjg g = this.gsjgDao.get(Long.valueOf(sbgsJc));
            if (g instanceof Nbgs) {
                condition.add(" this_.sbgs_id = :sbgs_id ");
                param.put("sbgs_id", g.getId());
            } else {
//                if ((((Nbbm) g).getSfgyz() == null || !(((Nbbm) g).getSfgyz()))
//                        && ((Nbbm) g).getKhckList() != null && ((Nbbm) g).getKhckList().size() > 0) {
//                    Long[] ckIds = new Long[((Nbbm) g).getKhckList().size()];
//                    for (int i = 0; i < ((Nbbm) g).getKhckList().size(); i++) {
//                        ckIds[i] = ((Nbbm) g).getKhckList().get(i).getId();
//                    }
//
//                    condition.add(" t.ck_id in (:glck_id) ");
//                    param.put("glck_id", ckIds);
//                }
                condition.add(" this_.sbbm_id = :sbbm_id ");
                param.put("sbbm_id", g.getId());
                condition.add(" this_.sbbm_id = :sbbm_id ");
                param.put("sbbm_id", g.getId());
            }
        }
        if (StringUtils.isNotEmpty(cggsJc)) {
            condition.add(" g3_.id = :cggs_id ");
            param.put("cggs_id", cggsJc);
        }

        if (StringUtils.isNotEmpty(qk)) {
            condition.add(" this_.qk=:qk ");
            param.put("qk", qk.equals("true"));
        }

        if (gysId != null) {
            condition.add(" cgd4_.gys_id=:gysId ");
            param.put("gysId", gysId);
        }

        condition.add(" this_.rkrq<=to_date('" + endDateStr + " 23:59:59','yyyy-MM-dd hh24:mi:ss') ");
        //String dataRegion = RegionDao.createSQLDataRegion(null, "t");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where this_.deleted!=1 and " + StringUtils.join(condition, " and ");

        sql += whereSql + " and " + createSqlCkDataRegion(null, "this_");

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    private String createSqlCkDataRegion(String joinStr, String alias) {
        Yg yg = this.ygDao.get(YgService.getCurrentYg().getId());
        List<Ck> list = yg.getCkList();
        if (list != null && list.size() > 0) {
            String ids = "";
            for (Ck ck : list) {
                ids += ck.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);

            String joinStrName = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String aliasName = StringUtils.isEmpty(alias) ? "" : alias + ".";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 2:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
                case 3:
                    regionSql += " {0} ({1}ck_id in (" + ids + "))";
                    break;
            }
            return regionSql.replace("{0}", joinStrName).replace("{1}", aliasName);
        } else {
            List<Gsjg> gslist = yg.getSsjgList();
            String ids = "";
            for (Gsjg gsjg : gslist) {
                ids += gsjg.getId() + ",";
            }
            ids = ids.substring(0, ids.length() - 1);

            String joinStrName = StringUtils.isEmpty(joinStr) ? "" : joinStr + " and ";
            String aliasName = StringUtils.isEmpty(alias) ? "" : alias + ".";
            String regionSql = "";
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    regionSql += " {0} 1=1";
                    break;
                case 1:
                    regionSql += " {0} {1}createOrgan_id in (select sjjg_id from T_SYS_GSJG gs where gs.id in (" + ids + "))";
                    break;
                case 2:
                    regionSql += " {0} {1}createOrgan_id in (select sjjg_id from T_SYS_GSJG gs where gs.id in (" + ids + "))";
                    break;
                case 3:
                    regionSql += " {0} {1}createDepartment_id =" + YgService.getCurrentYgGsId();
                    regionSql += " {0} {1}createUser_id =" + YgService.getCurrentYg().getId();
                    break;
            }
            return regionSql.replace("{0}", joinStrName).replace("{1}", aliasName);
        }

    }

    private String formatDate(Date date) {
        if (date == null)
            date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String s = sdf.format(date);
        return s;
    }

    /**
     * 查找所有的卡号
     *
     * @return
     */
    public List<Kh> findKh() {
        String hql = "from Kh where deleted = 0 " + RegionDao.createDataRegion("and");
        return find(hql);
    }

    public List<Map> findKhList(String sbgsJc, String cggsJc, String code, Long ckId, String jtwzname, String khbm, Date endDate, String wzmc, String qk) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String endDateStr = formatDate(endDate);
        String currentDateStr = formatDate(new Date());

        String yggl = StringUtils.EMPTY;
        if (existsSQLQuery("select 1 from t_sys_yg_bmfw where yg_id = ?", YgService.getCurrentYg().getId())) {
            yggl = " inner join v_wzbm_gg_yg_search bmfw  on this_.JTWZ_ID = bmfw.ggid and bmfw.yg_id=" + YgService.getCurrentYg().getId();
            //where = " bmfw.yg_id = " + YgService.getCurrentYg().getId();
        } else if (existsSQLQuery("select 1 from v_wzbm_gg_search  where gsid = ?", YgService.getCurrentYgGsId())) {
            yggl = " inner join v_wzbm_gg_search bmfw on this_.JTWZ_ID = bmfw.ggid and bmfw.gsid=" + YgService.getCurrentYgGsId();
        }

        String sqlColumn = "select t.id as \"id\", t.khbm as \"khbm\", t.qk as \"qk\", t.qkr as \"qkr\",t.jtwz_id as \"jtwz_id\"," +
                " t.jtwz_code as \"jtwz_code\",t.jtwz_oldcode as \"jtwz_oldcode\", t.jtwz_name as \"jtwz_name\",t.jtwz_ggxh as \"jtwz_ggxh\"," +
                " t.cgd_id as \"cgd_id\",t.cgd_cgdh as \"cgd_cgdh\", t.cd_id as \"cd_id\", t.cd_z as \"cd_z\"," +
                "  t.dw_z as \"dw_z\", t.ck_id as \"ck_id\", t.ck_mc as \"ck_mc\"," +
                "  t.sl as \"sl\",t.bz as \"bz\",t.chd as \"chd\",t.js as \"js\",t.jz as \"jz\",t.wsdj as \"wsdj\"," +
                " t.wsje as \"wsje\",t.zlqk as \"zlqk\", t.kkdsl as \"kkdsl\", t.lastUpdated as \"lastUpdated\",t.rkrq as \"rkrq\" ,t.sbgs_jc as \"sbgs_jc\",t.sbgs_id as \"sbgs_id\",t.sbbm_id as \"sbbm_id\",t.sbbm_qc as \"sbbm_qc\",t.cggs_id as \"cggs_id\",t.cggs_jc as \"cggs_jc\",t.createuser_id as \"createuser_id\"";
        String sqlBase = " (select this_.id as id, khbm as khbm,qk as qk,(select createuser5_.xm from T_SYS_YG createuser5_  where this_.CREATEUSER_ID =createuser5_.id) as qkr,jtwz1_.id as jtwz_id,jtwz1_.code as jtwz_code,jtwz1_.oldcode as jtwz_oldcode,jtwz1_.name as jtwz_name,jtwz1_.cz||' '||jtwz1_.ggxh as jtwz_ggxh,cgd4_.id as cgd_id," +
                "cgd4_.cgdh as cgd_cgdh,this_.CD_SJZDZ_ID as cd_id, (select cd2_.z from T_SYS_SJZDZ cd2_ where  this_.CD_SJZDZ_ID = cd2_.id) as cd_z,jtwz1_.dw as dw_z, this_.CK_ID as ck_id,(select ck3_.mc  from T_SYS_CK ck3_ where this_.CK_ID = ck3_.id) as ck_mc," +
                "this_.sl+NVL((select sum(xs.ck_sbsl) from T_BIZ_XSMX xs,T_BIZ_XSD xsd where xs.xsd_id=xsd.id and xs.kh_id=this_.id and xsd.deleted!=1 and xsd.yck=1 and xsd.cksj between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "-NVL((select sum(cg.rksl) from T_BIZ_CGDMX cg where cg.cgd_id=cgd4_.id and cg.kh_id=this_.id and cgd4_.deleted!=1 and cgd4_.yrk=1 and cgd4_.rksj between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "+NVL((select sum(llddmx.flsl) from T_BIZ_LLDMX llddmx,T_BIZ_LLD lld where llddmx.lld_id=lld.id and llddmx.kh_id=this_.id and lld.deleted!=1 and this_.ck_id=lld.ck_id and lld.flrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "+NVL((select sum(zkdmx.zcsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.kh_id=this_.id and zkd.deleted!=1 and  zkd.zkrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "-NVL((select sum(zkdmx.zrsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.new_kh_id =this_.id and zkd.deleted!=1  and zkd.zkrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0) as sl," +
                "this_.bz as bz,this_.chd as chd,this_.js as js,this_.jz as jz,this_.wsdj as wsdj," +
                "this_.wsdj * (this_.sl+NVL((select sum(xs.ck_sbsl) from T_BIZ_XSMX xs,T_BIZ_XSD xsd where xs.xsd_id=xsd.id and xs.kh_id=this_.id and xsd.deleted!=1 and xsd.yck=1 and xsd.cksj between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "-NVL((select sum(cg.rksl) from T_BIZ_CGDMX cg where cg.cgd_id=cgd4_.id and cg.kh_id=this_.id and cgd4_.deleted!=1 and cgd4_.yrk=1 and cgd4_.rksj between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "+NVL((select sum(llddmx.flsl) from T_BIZ_LLDMX llddmx,T_BIZ_LLD lld where llddmx.lld_id=lld.id and llddmx.kh_id=this_.id and lld.deleted!=1 and this_.ck_id=lld.ck_id and lld.flrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "+NVL((select sum(zkdmx.zcsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.kh_id=this_.id and zkd.deleted!=1 and  zkd.zkrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "-NVL((select sum(zkdmx.zrsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.new_kh_id =this_.id and zkd.deleted!=1 and  zkd.zkrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)) as wsje," +
                "this_.zlqk as zlqk,cgd4_.gys_id as gys," +
                "this_.kkdsl+NVL((select sum(xs.ck_sbsl) from T_BIZ_XSMX xs,T_BIZ_XSD xsd where xs.xsd_id=xsd.id and xs.kh_id=this_.id and xsd.deleted!=1 and  xsd.cksj between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "-NVL((select sum(cg.rksl) from T_BIZ_CGDMX cg where cg.cgd_id=cgd4_.id and cg.kh_id=this_.id and cgd4_.deleted!=1 and cgd4_.yrk=1  and cgd4_.rksj between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0) " +
                "+NVL((select sum(llddmx.flsl) from T_BIZ_LLDMX llddmx,T_BIZ_LLD lld where llddmx.lld_id=lld.id and llddmx.kh_id=this_.id and lld.deleted!=1 and this_.ck_id=lld.ck_id and lld.flrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "+NVL((select sum(zkdmx.zcsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.kh_id=this_.id and zkd.deleted!=1 and  zkd.zkrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0)" +
                "-NVL((select sum(zkdmx.zrsl) from T_BIZ_ZKDMX zkdmx,T_BIZ_ZKD zkd where zkdmx.zkd_id=zkd.id and zkdmx.new_kh_id =this_.id and zkd.deleted!=1 and  zkd.zkrq between to_date('" + endDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss') and to_date('" + currentDateStr + " 23:59:59','yyyy-mm-dd hh24:mi:ss')),0) as kkdsl," +
                "this_.last_updated as lastUpdated,this_.rkrq as rkrq, this_.sbgs_id as sbgs_id,(select g1_.jc from t_SYS_GSJG g1_ where this_.sbgs_id = g1_.id)  as sbgs_jc, this_.sbbm_id as sbbm_id,(select g2_.qc from t_SYS_GSJG g2_ where this_.sbbm_id = g2_.id) as sbbm_qc,this_.cggs_id as cggs_id,(select g3_.jc from t_SYS_GSJG g3_ where this_.cggs_id = g3_.id)  as  cggs_jc,this_.CREATEUSER_ID as createuser_id,this_.CREATEORGAN_ID as CREATEORGAN_ID,this_.Createdepartment_Id as Createdepartment_Id" +
                " from T_BIZ_KH this_" + yggl +
                " left outer join T_BIZ_CGD cgd4_ on this_.CGD_ID = cgd4_.id" +
                " left outer join T_SYS_GG jtwz1_ on this_.JTWZ_ID = jtwz1_.id where this_.deleted = 0 ) t";

        if (StringUtils.isNotEmpty(khbm)) {
            condition.add(" t.khbm like:khbm ");
            param.put("khbm", "%" + khbm + "%");
        }

        if (StringUtils.isNotEmpty(jtwzname)) {
            condition.add(" jtwz1_.cz||' '||jtwz1_.ggxh like:jtwz_ggxh ");
            param.put("jtwz_ggxh", "%" + jtwzname + "%");
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" (t.jtwz_code like :jtwz_code or t.jtwz_oldcode like :jtwz_oldcode) ");
            param.put("jtwz_code", code + "%");
            param.put("jtwz_oldcode", code + "%");
        }

        if (ckId != null) {
            condition.add(" t.ck_id=:ck_id ");
            param.put("ck_id", ckId);
        }

        if (StringUtils.isNotEmpty(sbgsJc)) {
            Gsjg g = this.gsjgDao.get(Long.valueOf(sbgsJc));
            if (g instanceof Nbgs) {
                condition.add(" t.sbgs_id = :sbgs_id ");
                param.put("sbgs_id", g.getId());
            } else {
                condition.add(" t.sbbm_id = :sbbm_id ");
                param.put("sbbm_id", g.getId());
            }
        }
        if (StringUtils.isNotEmpty(cggsJc)) {
            condition.add(" t.cggs_id = :cggs_id ");
            param.put("cggs_id", cggsJc);
        }

        if (StringUtils.isNotEmpty(qk)) {
            condition.add(" t.qk=:qk ");
            param.put("qk", qk.equals("true"));
        }

        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" t.jtwz_name like :wzmc ");
            param.put("wzmc", wzmc);
        }

        condition.add(" t.rkrq<=to_date('" + endDateStr + " 23:59:59','yyyy-MM-dd hh24:mi:ss') ");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by t.jtwz_code asc";
        String resultSql = sqlColumn + " from (" + sqlBase + ") " + whereSql + " and " + createSqlCkDataRegion(null, "t") + orderSql;

        List<Map> list = createSQLQuery(resultSql)
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    /**
     * 盘库查询
     *
     * @param ckId
     */
    public List<Map> findPkList(Long ckId) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlBase = " select kh.khbm,gg.oldcode,gg.code, gg.ggxh,gg.th,gg.cz, gg.dw,gg.name,ck.mc, " +
                " (NVL((select sum(cg.rksl) from t_biz_cgdmx cg where cg.cgd_id = cgd4_.id and cg.kh_id = kh.id " +
                "  and cgd4_.deleted != 1 and cgd4_.yrk = 1 and cgd4_.rksj between to_date(to_char(add_months(last_day(sysdate), -1), " +
                " 'yyyy-MM-dd') || '23:59:59','yyyy-MM-dd hh24:mi:ss') and sysdate), 0) + " +
                "  NVL((select sum(zkdmx.zrsl)" +
                "  from T_BIZ_ZKDMX zkdmx, T_BIZ_ZKD zkd where zkdmx.zkd_id = zkd.id and zkdmx.new_kh_id = kh.id " +
                "  and zkd.deleted != 1 and zkd.zkrq between " +
                "  to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd hh24:mi:ss') and sysdate)," +
                "  0)) as byrk,(NVL((select sum(xs.ck_sbsl) from t_biz_xsmx xs, T_biz_xsd xsd where xs.xsd_id = xsd.id " +
                " and xs.kh_id = kh.id " +
                " and xsd.deleted != 1 " +
                " and xsd.yck = 1 " +
                " and xsd.cksj between " +
                " to_date(to_char(add_months(last_day(sysdate), -1), " +
                " 'yyyy-MM-dd') || '23:59:59', " +
                " 'yyyy-MM-dd hh24:mi:ss') and sysdate), 0) +" +
                "  NVL((select sum(zkdmx.zcsl) " +
                "  from T_BIZ_ZKDMX zkdmx, T_BIZ_ZKD zkd where zkdmx.zkd_id = zkd.id and zkdmx.kh_id = kh.id and zkd.deleted != 1 and zkd.zkrq between " +
                "  to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || '23:59:59','yyyy-MM-dd hh24:mi:ss') and sysdate), 0) + " +
                "  NVL((select sum(llddmx.flsl) from T_BIZ_LLDMX llddmx, T_BIZ_LLD lld where llddmx.lld_id = lld.id and llddmx.kh_id = kh.id  " +
                "  and lld.deleted != 1 and kh.ck_id = lld.ck_id and lld.flrq between to_date(to_char(add_months(last_day(sysdate), -1),  " +
                "  'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd hh24:mi:ss') and sysdate),0)) as byck, " +
                "  (kh.sl + NVL((select sum(xs.ck_sbsl) from t_biz_xsmx xs, T_biz_xsd xsd where xs.xsd_id = xsd.id and xs.kh_id = kh.id  " +
                "  and xsd.deleted != 1 and xsd.yck = 1  " +
                "  and xsd.cksj between sysdate and sysdate),  " +
                "  0) - NVL((select sum(cg.rksl)  " +
                "  from t_biz_cgdmx cg  " +
                "  where cg.cgd_id = cgd4_.id  " +
                "  and cg.kh_id = kh.id  " +
                "  and cgd4_.deleted != 1  " +
                "  and cgd4_.yrk = 1  " +
                "  and cgd4_.rksj between sysdate and sysdate), 0) +  " +
                "  NVL((select sum(llddmx.flsl) from T_BIZ_LLDMX llddmx, T_BIZ_LLD lld  " +
                "  where llddmx.lld_id = lld.id  " +
                "  and llddmx.kh_id = kh.id  " +
                "  and lld.deleted != 1  " +
                "  and kh.ck_id = lld.ck_id  " +
                "  and lld.flrq between sysdate and sysdate),0) + NVL((select sum(zkdmx.zcsl)  " +
                "  from T_BIZ_ZKDMX zkdmx, T_BIZ_ZKD zkd  " +
                "  where zkdmx.zkd_id = zkd.id  " +
                "  and zkdmx.kh_id = kh.id  " +
                "  and zkd.deleted != 1  " +
                "  and zkd.zkrq between sysdate and sysdate), 0) - NVL((select sum(zkdmx.zrsl)  " +
                "  from T_BIZ_ZKDMX zkdmx, T_BIZ_ZKD zkd  " +
                "  where zkdmx.zkd_id = zkd.id  " +
                "  and zkdmx.new_kh_id = kh.id  " +
                "  and zkd.deleted != 1  " +
                "  and zkd.zkrq between sysdate and sysdate), 0)) as bykc, " +
                "  (kh.sl + NVL((select sum(xs.ck_sbsl)  " +
                "   from t_biz_xsmx xs, T_biz_xsd xsd  " +
                "  where xs.xsd_id = xsd.id  " +
                "   and xs.kh_id = kh.id  " +
                "   and xsd.deleted != 1  " +
                "   and xsd.yck = 1  " +
                "  and xsd.cksj between  " +
                "  to_date(to_char(add_months(last_day(sysdate), -1),  " +
                "  'yyyy-MM-dd') || '23:59:59',  " +
                "  'yyyy-MM-dd hh24:mi:ss') and sysdate), 0) -  " +
                "   NVL((select sum(cg.rksl)  " +
                "   from t_biz_cgdmx cg  " +
                "  where cg.cgd_id = cgd4_.id  " +
                "  and cg.kh_id = kh.id  " +
                "  and cgd4_.deleted != 1  " +
                "  and cgd4_.yrk = 1  " +
                "  and cgd4_.rksj between  " +
                "   to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd hh24:mi:ss') and sysdate),  0) +  " +
                "  NVL((select sum(llddmx.flsl)  " +
                "  from T_BIZ_LLDMX llddmx, T_BIZ_LLD lld  " +
                "   where llddmx.lld_id = lld.id  " +
                "   and llddmx.kh_id = kh.id  " +
                "   and lld.deleted != 1  " +
                "   and kh.ck_id = lld.ck_id  " +
                "    and lld.flrq between  " +
                "   to_date(to_char(add_months(last_day(sysdate), -1), 'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd hh24:mi:ss') and sysdate), 0) +  " +
                "   NVL((select sum(zkdmx.zcsl)  " +
                "   from T_BIZ_ZKDMX zkdmx, T_BIZ_ZKD zkd  " +
                "    where zkdmx.zkd_id = zkd.id  " +
                "    and zkdmx.kh_id = kh.id  " +
                "    and zkd.deleted != 1  " +
                "   and zkd.zkrq between  " +
                "    to_date(to_char(add_months(last_day(sysdate), -1),'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd hh24:mi:ss') and sysdate), 0) -  " +
                "   NVL((select sum(zkdmx.zrsl)  " +
                "   from T_BIZ_ZKDMX zkdmx, T_BIZ_ZKD zkd  " +
                "   where zkdmx.zkd_id = zkd.id  " +
                "  and zkdmx.new_kh_id = kh.id  " +
                "  and zkd.deleted != 1  " +
                "  and zkd.zkrq between  " +
                "  to_date(to_char(add_months(last_day(sysdate), -1), 'yyyy-MM-dd') || '23:59:59','yyyy-MM-dd hh24:mi:ss') and sysdate),0)) as sykc  " +
                "  from t_biz_kh kh  " +
                "  inner join t_sys_gg gg  " +
                "    on kh.jtwz_id = gg.id  " +
                "  left join t_biz_cgd cgd4_  " +
                "    on kh.cgd_id = cgd4_.id  " +
                "  left join t_sys_ck ck  " +
                "    on kh.ck_id = ck.id  " +
                " where " +
                " kh.qk = 0  " +
                " and kh.rkrq <= sysdate and kh.deleted != 1 ";


        String order = " order by gg.code asc ";

        if (ckId != null) {
            condition.add(" ck_id=:ck_id ");
            param.put("ck_id", ckId);
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String resultSql = sqlBase + whereSql + order;

        List<Map> list = createSQLQuery(resultSql)
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    public List<Map> findSskcGdWzmxFromKhIds(List<Long> khIds) {
        Criteria criteria = getSession().createCriteria(Kh.class)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("ck", "ck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Projections.sqlProjection("this_.id as id", new String[]{"id"}, new Type[]{Hibernate.LONG}))
                        .add(Property.forName("khbm").as("kh.khbm"))
                        .add(Property.forName("kkdsl").as("kkdsl"))
                        .add(Property.forName("wsdj").as("wsdj"))
                        .add(Property.forName("chd").as("chd"))
                        .add(Property.forName("jz").as("jz"))
                        .add(Property.forName("wzgg.id").as("wzgg.id"))
                        .add(Property.forName("wzgg.code").as("wzgg.code"))
                        .add(Property.forName("wzgg.name").as("wzgg.name"))
                        .add(Projections.sqlProjection("wzgg1_.cz||' '||wzgg1_.ggxh as \"wzgg.ggxh\"", new String[]{"wzgg.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                        .add(Property.forName("wzgg.th").as("wzgg.th"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("sl").as("sl"))
                        .add(Property.forName("js").as("js"))
                ).add(Restrictions.in("id", khIds))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public Page<Map> findjgdSskcList(String sbgsJc, String cggsJc, String code, String ckmc, Long gysId, Long ckId, String jtwzname, String cgdh, String khbm, String qk, String wzmc, String rkdh, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();

        String sqlColumn = " select kh.khbm as \"khbm\",gg.code as \"code\",gg.name as \"name\" ,gg.cz || '  ' || gg.ggxh as \"ggxh\"," +
                "cd.z as \"cd\",gg.dw as \"dw\",kh.sl as \"zl\",kh.js as \"js\",kh.jz as \"jz\",kh.sku as \"sku\", " +
                "kh.kkdsl as \"kkdsl\",cgdmx.bhsj as \"wsdj\",cgdmx.bhsj*cgdmx.sbsl as \"wsje\", " +
                "cgd.cgdh as \"cgdh\",ck.mc as \"ck\" ,gys.qc as \"gys\", " +
                "sbgs.qc as \"sbgs\",cggs.qc as \"cggs\",kh.rkrq as \"rkrq\",ck.id as \"ck_id\", " +
                " kh.sbgs_id as \"sbgs_id\",kh.sbbm_id as \"sbbm_id\",kh.cggs_id as \"cggs_id\"," +
                "kh.gys_id as \"gys_id\",kh.qk as \"qk\",kh.id as \"id\",rkdh as \"rkdh\",kh.jtwz_id as \"jtwz_id\"," +
                "kh.cd_sjzdz_id as \"cd_id\" ";


        String fromColumn = " from t_biz_kh kh  " +
                "left join t_sys_gg gg on gg.id = kh.jtwz_id " +
                "left join t_biz_cgd cgd on cgd.id = kh.cgd_id " +
                "left join t_biz_cgdmx cgdmx  on cgdmx.id = kh.cgdmx_id " +
                "left join t_sys_sjzdz cd on cd.id = kh.cd_sjzdz_id  " +
                "left join t_sys_ck ck on ck.id = kh.ck_id " +
                "left join t_sys_gsjg gys on gys.id = kh.gys_id " +
                "left join t_sys_gsjg sbgs on sbgs.id = kh.sbgs_id " +
                "left join t_sys_gsjg cggs on cggs.id = kh.cggs_id  ";


        if (StringUtils.isNotEmpty(khbm)) {
            condition.add(" khbm like:khbm ");
            param.put("khbm", "%" + khbm + "%");
        }

        if (StringUtils.isNotEmpty(rkdh)) {
            condition.add(" rkdh like:rkdh ");
            param.put("rkdh", "%" + rkdh + "%");
        }

        if (StringUtils.isNotEmpty(jtwzname)) {
            condition.add(" ggxh like:ggxh ");
            param.put("ggxh", "%" + jtwzname + "%");
        }

        if (StringUtils.isNotEmpty(code)) {
            condition.add(" code like :code ");
            param.put("code", "%" + code + "%");
        }

        if (ckId != null) {
            condition.add(" ck_id =:ck ");
            param.put("ck", ckId);
        }

        if (StringUtils.isNotEmpty(sbgsJc)) {
            Gsjg g = this.gsjgDao.findGsjgFromQc(sbgsJc);
            if (null != g && g.getId() > 0) {
                condition.add(" kh.sbgs_id = :sbgs_id ");
                param.put("sbgs_id", g.getId());
            } else {
                condition.add(" kh.sbgs_id = :sbgs_id ");
                param.put("sbgs_id", -100);
            }
        }

        if (StringUtils.isNotEmpty(cggsJc)) {
            condition.add(" kh.cggs_id = :cggs_id ");
            param.put("cggs_id", cggsJc);
        }

        if (StringUtils.isNotEmpty(cgdh)) {
            condition.add(" cgd.cgdh like :cgdh ");
            param.put("cgdh", cgdh + "%");
        }

        if (gysId != null) {
            condition.add(" gys_id=:gysId ");
            param.put("gysId", gysId);
        }

        if (StringUtils.isNotEmpty(wzmc)) {
            condition.add(" name like :name ");
            param.put("name", wzmc);
        }

        condition.add(" (jgzt is null or jgzt =0)");

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " where " + StringUtils.join(condition, " and ");
        String orderSql = " order by kh.id desc";


        String countSql = "select count(1)  " + fromColumn + whereSql + " and " + createSqlCkDataRegion(null, "kh");
        String resultSql = sqlColumn + fromColumn + whereSql + " and " + createSqlCkDataRegion(null, "kh") + orderSql;


        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 查询入库总重量
     *
     * @param xsdh
     * @param carNo
     * @param fhrq
     * @return
     */
    public List<Map> findSjdjList(String xsdh, String carNo, String fhrq) {
        String sql = "select nvl(sum(k.rksl),0) rksl from t_biz_kh k where k.xsxt_xsdh = ? and k.carno = ? and to_char(k.xsxt_fhrq,'yyyy-MM-dd') = ?";
        return (List<Map>) this.createSQLQuery(sql, xsdh, carNo, fhrq).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    /**
     * 根据销售系统子项号查询具体物资
     *
     * @param xsdh
     * @return
     */
    public Gg findJtwz(String xsdh) {
        String hql = "from Kh where xsxt_xsdh = ?";
        List<Kh> list = this.find(hql, xsdh);
        return list.get(0).getJtwz();
    }


    public List<Kh> findKhForSjdj(String xsdh, String carno, String fhrq) {
        String hql = "from Kh where xsxt_xsdh = ? and carNo = ? and  to_char(xsxt_fhrq,'yyyy-MM-dd') = ?";
        return this.find(hql, xsdh, carno, fhrq);
    }

    @Autowired
    public void setYgDao(YgDao ygDao) {
        this.ygDao = ygDao;
    }

    @Autowired
    public void setGsjgDao(GsjgDao gsjgDao) {
        this.gsjgDao = gsjgDao;
    }


}
